SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE  FUNCTION dbo.fnGetSpecificNAVDatePrice
(
	-- Add the parameters for the function here
	@PriceDate DATE, @LXID varchar(50), @Field VARCHAR(MAX), @NAVDateOffsetNum INT
	
	)
RETURNS   DECIMAL(38,10)
AS
BEGIN
	DECLARE @ResultVar DECIMAL(38,10)
--	DECLARE @ExportId  INT = (SELECT MAX(e.ExportId) FROM FSFundModel.Reporting.Export e WHERE e.FundId ='FSIC') 
--	DECLARE @LXID VARCHAR(50) ='LX118871' 
--	 DECLARE @Field VARCHAR(MAX) = 'Mid'
--	DECLARE @PriceDate DATETIME	 = (
	
--	SELECT TOP 1 p.PriceDate FROM FSFundModel.Reporting.Pricing p 
--	WHERE p.PricingId =  @LXID
--	AND p.ExportId = @ExportId)

----SELECT @PriceDate
	
	DECLARE @ShortNAVDate AS DATE = @PriceDate 
--    --SELECT @ShortNAVDate , @PriceDate
--  SET @ShortNAVDate = @PriceDate
  
  DECLARE @TVFD AS DATE  = (SELECT  NAVDate FROM ReportingScripts.dbo.tvfNAVDatesByFund(@PriceDate, @NAVDateOffsetNUM,0,0) )
	-- Add the T-SQL statements to compute the return value here
SET @ResultVar= (	
		SELECT 
		TOP 1
		 CASE WHEN @Field = 'Mid' THEN prc.Mid		
		WHEN @Field = 'Bid' THEN prc.Bid
		WHEN @Field = 'Ask' THEN prc.Offer
		WHEN @Field = 'Depth' THEN prc.Volume
	
		END
	 FROM FSFundModel.dbo.Pricing prc 


	 

		WHERE prc.PriceDate =  @TVFD
		AND prc.PriceDate IS NOT NULL 
		AND LTRIM(RTRIM(prc.PricingId)) = LTRIM(RTRIM(@LXID))
		--AND prc.ExportId =  @ExportId 
		ORDER BY prc.PriceDate DESC 
		 )


	  RETURN @ResultVar 
END
GO
